﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.Mvc;
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.HSSF.Util;

namespace TBM
{
    /// <summary>
    /// 导出Excel
    /// </summary>
    public class ExcelResult: ActionResult
    {
        public ExcelResult(DataSet data, string filename)
        {
            this.Data = data;
            this.FileName = filename;
        }

        public DataSet Data { get; set; }
        public String FileName { get; set; }

        public override void ExecuteResult(ControllerContext context)
        {
            if (Data == null)
            {
                new EmptyResult().ExecuteResult(context);
                return;
            }

            context.HttpContext.Response.ContentType = "application/vnd.ms-excel";
            context.HttpContext.Response.AppendHeader("content-disposition", "attachment;filename=" + FileName + ".xls;");

            HSSFWorkbook workbook = new HSSFWorkbook();
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            
            // 设置简介
            dsi.Company = Core.Config.SiteName;
            workbook.DocumentSummaryInformation = dsi;

            foreach (DataTable table in Data.Tables)
            {
                CreateSheet(table, workbook);
            }

            workbook.Write(context.HttpContext.Response.OutputStream);
            context.HttpContext.Response.End();
        }


        private void CreateSheet(DataTable table, HSSFWorkbook workbook)
        {
            HSSFSheet sheet = workbook.CreateSheet(table.TableName) as HSSFSheet;
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();

            si.Subject = table.TableName;
            workbook.SummaryInformation = si;

            // 定义样式
            HSSFCellStyle CStyleForTitle = workbook.CreateCellStyle() as HSSFCellStyle;
            HSSFCellStyle CStyleForNormal = workbook.CreateCellStyle() as HSSFCellStyle;
            HSSFCellStyle CStyleForEven = workbook.CreateCellStyle() as HSSFCellStyle;
            HSSFFont FStyleForTitle = workbook.CreateFont() as HSSFFont;
            HSSFFont FStyleForNormal = workbook.CreateFont() as HSSFFont;
            HSSFPalette palette = workbook.GetCustomPalette();

            FStyleForTitle.Color = HSSFColor.White.Index;
            FStyleForTitle.IsBold = true;

            CStyleForTitle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            CStyleForTitle.BottomBorderColor = HSSFColor.LightOrange.Index;
            CStyleForTitle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            CStyleForTitle.LeftBorderColor = HSSFColor.LightOrange.Index;
            CStyleForTitle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            CStyleForTitle.RightBorderColor = HSSFColor.LightOrange.Index;
            CStyleForTitle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            CStyleForTitle.TopBorderColor = HSSFColor.LightOrange.Index;
            CStyleForTitle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
            CStyleForTitle.FillForegroundColor = HSSFColor.LightOrange.Index;
            CStyleForTitle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            CStyleForTitle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            CStyleForTitle.SetFont(FStyleForTitle);

            CStyleForNormal.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            CStyleForNormal.BottomBorderColor = HSSFColor.Gold.Index;
            CStyleForNormal.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            CStyleForNormal.LeftBorderColor = HSSFColor.Gold.Index;
            CStyleForNormal.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            CStyleForNormal.RightBorderColor = HSSFColor.Gold.Index;
            CStyleForNormal.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            CStyleForNormal.TopBorderColor = HSSFColor.Gold.Index;
            CStyleForNormal.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;

            CStyleForEven.CloneStyleFrom(CStyleForNormal);
            CStyleForEven.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
            CStyleForEven.FillForegroundColor = HSSFColor.LemonChiffon.Index;

            // 创建标题
            int num = 0;
            int colLength = table.Columns.Count;
            HSSFRow row = (HSSFRow)sheet.CreateRow(num);

            row.Height = 450;
            for (int i = 0; i < colLength; i++)
            {
                row.CreateCell(i);
                row.GetCell(i).CellStyle = CStyleForTitle;
                row.GetCell(i).SetCellValue(table.Columns[i].ColumnName);
            }

            num = 0;
            while (num++ < table.Rows.Count)
            {
                row = (HSSFRow)sheet.CreateRow(num);
                row.Height = 300;

                for (int i = 0; i < colLength; i++)
                {
                    row.CreateCell(i);
                    row.GetCell(i).CellStyle = num % 2 == 0 ? CStyleForEven : CStyleForNormal;
                    row.GetCell(i).SetCellValue(table.Rows[num - 1][i].ToString());
                }
            }
        }
    }
}
